﻿using MediatR;
using Microsoft.Data.SqlClient;
using S.EliminationOrDath.Infrastructure;
using S.EliminationOrDath.Read.API.Dtos.ProductionStatistics;
using static S.EliminationOrDath.Read.API.Application.Command.ProductionStatisticsCommand.SheepProductionStatisticsCommand;

namespace S.EliminationOrDath.Read.API.Application.CommandHandlers.ProductionStatisticsCommandHandler
{
    /// <summary>
    /// 中介者实现层—种母生产报表
    /// </summary>
    public class SheepProductionStatisticsCommandHandler : IRequestHandler<GetFertilityDataStatisticsCommand, List<ProductionStatisticsDto>>, IRequestHandler<GetBreedingstatisticsCommand, List<BreedingstatisticsDto>>
    {
        private readonly DBHelper db;

        public SheepProductionStatisticsCommandHandler(DBHelper db)
        {
            this.db = db;
        }
        
        /// <summary>
        /// 查询分娩数据
        /// </summary>
        /// <param name="request"></param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public async Task<List<ProductionStatisticsDto>> Handle(GetFertilityDataStatisticsCommand request, CancellationToken cancellationToken)
        {
            //按年查
            if (request.date == "年")
            {
                int year;
                if (string.IsNullOrEmpty(request.kdate))
                {
                    year = DateTime.Now.Year; // 当request.kdate为空时，使用当前年份
                }
                else
                {
                    if (!int.TryParse(request.kdate, out year)) // 尝试将request.kdate转换为整数，如果失败，可以返回错误或使用默认值
                    {
                        return null; // 或者您可以返回一个错误信息，或者设置year为一个默认值
                    }
                }

                var sql = @"SELECT 
                    count(*) as 'count',
                    SUM(HealthyNumber) AS HealthyNumber,
                    SUM(TotalWeightOfHealthyLambs) AS TotalWeightOfHealthyLambs,
                    SUM(LambNumber) AS LambNumber,
                    SUM(DeformityNumber) AS DeformityNumber,
                    SUM(StillbirthsNumber) AS StillbirthsNumber,
                    SUM(MummyNumber) AS MummyNumber,
                    SUM(FreaksNumber) AS FreaksNumber 
                FROM BreedingHistoryModel 
                WHERE YEAR(DeliveryDate) = @Year;";

                SqlParameter[] parameters = { new SqlParameter("@Year", year) };

                return await db.GetListDataBySqlWhere<ProductionStatisticsDto>(sql, parameters);
            }
            //按月查
            else if (request.date == "月")
            {
                int year, month;
                if (string.IsNullOrEmpty(request.kdate))
                {
                    year = DateTime.Now.Year; // 当request.kdate为空时，使用当前年份
                    month = DateTime.Now.Month; // 使用当前月份
                }
                else
                {
                    // 这里假设request.kdate是一个形如"2023-08"的字符串
                    string[] parts = request.kdate.Split('-');
                    if (parts.Length != 2 || !int.TryParse(parts[0], out year) || !int.TryParse(parts[1], out month))
                    {
                        return null; // 或者您可以返回一个错误信息，或者设置year和month为默认值
                    }
                }

                var sql = @"SELECT 
                    count(*) as 'count',
                    SUM(HealthyNumber) AS HealthyNumber,
                    SUM(TotalWeightOfHealthyLambs) AS TotalWeightOfHealthyLambs,
                    SUM(LambNumber) AS LambNumber,
                    SUM(DeformityNumber) AS DeformityNumber,
                    SUM(StillbirthsNumber) AS StillbirthsNumber,
                    SUM(MummyNumber) AS MummyNumber,
                    SUM(FreaksNumber) AS FreaksNumber 
                FROM BreedingHistoryModel 
                WHERE YEAR(DeliveryDate) = @Year AND MONTH(DeliveryDate) = @Month;";

                SqlParameter[] parameters = {
                    new SqlParameter("@Year", year),
                    new SqlParameter("@Month", month)
                };

                return await db.GetListDataBySqlWhere<ProductionStatisticsDto>(sql, parameters);
            }
            //按天查
            else if (request.date == "日")
            {
                int year, month, day;
                if (string.IsNullOrEmpty(request.kdate))
                {
                    year = DateTime.Now.Year; // 当request.kdate为空时，使用当前年份
                    month = DateTime.Now.Month; // 使用当前月份
                    day = DateTime.Now.Day; // 使用当前日期
                }
                else
                {
                    // 这里假设request.kdate是一个形如"2023-08-15"的字符串
                    string[] parts = request.kdate.Split('-');
                    if (parts.Length != 3 || !int.TryParse(parts[0], out year) || !int.TryParse(parts[1], out month) || !int.TryParse(parts[2], out day))
                    {
                        return null; // 或者您可以返回一个错误信息，或者设置year, month和day为默认值
                    }
                }

                var sql = @"SELECT 
                    count(*) as 'count',
                    SUM(HealthyNumber) AS HealthyNumber,
                    SUM(TotalWeightOfHealthyLambs) AS TotalWeightOfHealthyLambs,
                    SUM(LambNumber) AS LambNumber,
                    SUM(DeformityNumber) AS DeformityNumber,
                    SUM(StillbirthsNumber) AS StillbirthsNumber,
                    SUM(MummyNumber) AS MummyNumber,
                    SUM(FreaksNumber) AS FreaksNumber 
                FROM BreedingHistoryModel 
                WHERE YEAR(DeliveryDate) = @Year AND MONTH(DeliveryDate) = @Month AND DAY(DeliveryDate) = @Day;";

                SqlParameter[] parameters = {
                    new SqlParameter("@Year", year),
                    new SqlParameter("@Month", month),
                    new SqlParameter("@Day", day)
                };

                return await db.GetListDataBySqlWhere<ProductionStatisticsDto>(sql, parameters);
            }
            else
            {
                // 如果没有匹配的查询类型，您可以返回一个错误信息或默认值
                return null;
            }
        }
        /// <summary>
        /// 配种统计
        /// </summary>
        /// <param name="request"></param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public async Task<List<BreedingstatisticsDto>> Handle(GetBreedingstatisticsCommand request, CancellationToken cancellationToken)
        {
            //按年查
            if (request.date == "年")
            {
                int year;
                if (string.IsNullOrEmpty(request.kdate))
                {
                    year = DateTime.Now.Year; // 当request.kdate为空时，使用当前年份
                }
                else
                {
                    if (!int.TryParse(request.kdate, out year)) // 尝试将request.kdate转换为整数，如果失败，可以返回错误或使用默认值
                    {
                        return null; // 或者您可以返回一个错误信息，或者设置year为一个默认值
                    }
                }

                var sql = @"SELECT ReproductiveState, COUNT(*) as 'count'
                            FROM MaternalMating
                            WHERE ReproductiveState != 0 
                            AND ReproductiveState BETWEEN 1 AND 6
                            AND YEAR(CreateDate) = @Year
                            GROUP BY ReproductiveState;";

                SqlParameter[] parameters = { new SqlParameter("@Year", year) };

                return await db.GetListDataBySqlWhere<BreedingstatisticsDto>(sql, parameters);
            }
            //按月查
            else if (request.date == "月")
            {
                int year, month;
                if (string.IsNullOrEmpty(request.kdate))
                {
                    year = DateTime.Now.Year; // 当request.kdate为空时，使用当前年份
                    month = DateTime.Now.Month; // 使用当前月份
                }
                else
                {
                    // 这里假设request.kdate是一个形如"2023-08"的字符串
                    string[] parts = request.kdate.Split('-');
                    if (parts.Length != 2 || !int.TryParse(parts[0], out year) || !int.TryParse(parts[1], out month))
                    {
                        return null; // 或者您可以返回一个错误信息，或者设置year和month为默认值
                    }
                }

                var sql = @"SELECT ReproductiveState, COUNT(*) as 'count'
                            FROM MaternalMating
                            WHERE ReproductiveState != 0 
                            AND ReproductiveState BETWEEN 1 AND 6
                            AND YEAR(CreateDate) = @Year AND MONTH(CreateDate) = @Month
                            GROUP BY ReproductiveState;";

                SqlParameter[] parameters = {
                    new SqlParameter("@Year", year),
                    new SqlParameter("@Month", month)
                };

                return await db.GetListDataBySqlWhere<BreedingstatisticsDto>(sql, parameters);
            }
            //按天查
            else if (request.date == "日")
            {
                DateTime date;
                if (string.IsNullOrEmpty(request.kdate))
                {
                    date = DateTime.Now; // 当request.kdate为空时，使用当前日期
                }
                else
                {
                    // 尝试将request.kdate转换为日期，如果失败，可以返回错误或使用默认值
                    if (!DateTime.TryParse(request.kdate, out date))
                    {
                        return null; // 或者您可以返回一个错误信息，或者设置date为一个默认值
                    }
                }

                var sql = @"SELECT ReproductiveState, COUNT(*) as 'count'
                FROM MaternalMating
                WHERE ReproductiveState != 0 
                AND ReproductiveState BETWEEN 1 AND 6
                AND CAST(CreateDate AS DATE) = @Date
                GROUP BY ReproductiveState;";

                SqlParameter[] parameters = {
                    new SqlParameter("@Date", date.ToString("yyyy-MM-dd"))
                };

                return await db.GetListDataBySqlWhere<BreedingstatisticsDto>(sql, parameters);
            }
            else
            {
                // 如果没有匹配的查询类型，您可以返回一个错误信息或默认值
                return null;
            }
        }
    }
}
